package com.boss.fileapp.dao.impl;

import com.boss.fileapp.consts.TableName;
import com.boss.fileapp.dao.IBankOfferDataDao;
import com.boss.fileapp.entity.Offer;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class BankOfferDataDaoImpl extends BaseDaoImpl implements IBankOfferDataDao {

    @Override
    public List<Offer> selectHZOffer(String province,String sendBankCode) {
        List<String> params=new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" concat( avy.salary_month , pet.iden_no ) id, ")
                .append(" concat( avy.salary_month , pet.person_id ) bizKey,pet.iden_no idenNo,pet.person_name personName, ")
                .append(" smed.s1 realSalary, pet.sala_card_no salaCardNo, pet.sala_card_bank salaCardBank,avy.agency_code agencyCode, ")
                .append(" avy.agency_name agencyName ,avy.salary_month salaryMonth")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" on ").append("  pet.agency_id = smed.agency_id and pet.person_id=smed.person_id ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append("  pet.agency_id=avy.agency_id and smed.agency_id=avy.agency_id ")
                .append(" where ").append(" avy.mof_div_code = ? and avy.is_month_end = 0 and is_bank_data_created = 0 ")
                .append(" and is_pay_data_created = 1 ");
        params.add(province);
        if(!StringUtils.isEmpty(sendBankCode)){
            sb.append(" and pet.sala_card_bank=? ");
            params.add(sendBankCode);
        }
        sb.append(" order by avy.agency_code ");
        return super.getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(Offer.class), params.toArray());
    }

    @Override
    public List<String> selectSuccessPersonIden(){
        StringBuilder sb = new StringBuilder();
        sb.append("select back.iden_no from ")
                .append(TableName.SALARY_BANK_PAY_BACK)
                .append(" back where bank_back_code='S'");
        return super.getjdbcTemplate().queryForList(sb.toString(), String.class);
    }

    @Override
    public List<Map<String, Object>> selectMXOffer(List<String> agencyIds, List<String> columnList) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.sala_card_bank salaCardBank, ")
                .append(" aio.agency_code agencyCode, ")
                .append(" aio.agency_name agencyName, ")
                .append(" if(pet.state = '3', '在职', '离退休') state, ")
                .append(" if(pet.merit_pay_code is null or pet.merit_pay_code = '',0,pet.merit_pay_code) meritPayCode, ")
                .append(" ifnull(dept.dept_code,'999') deptId,")
                .append(" if(pet.sala_gr_code is null or pet.sala_gr_code = '',0,sala_gr_code) salaGrCode, ")
                .append(" concat(avy.salary_month,pet.person_id) personCode, ")
                .append(" ifnull(pet.show_order,'999') showOrder,")
                .append(" pet.sala_card_no salaCardNo, ")
                .append(" pet.person_name personName, ")
                .append(" pet.iden_no idenNo,")
                .append(" if(smed.s1 is null,0,smed.s1) ns1 ,")
                .append(" if(smed.s2 is null,0,smed.s2) ns2 ");
        if(!CollectionUtils.isEmpty(columnList)){
            for(String column : columnList){
                if(!"0".equals(column)){
                    sb.append(" , if(smed.").append(column).append(" is null ")
                            .append(" or smed.").append(column).append(" = 0, '0', smed.").append(column)
                            .append(") ").append(column);
                }
            }
        }
        sb.append(" from ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" on ").append(" pet.person_id = smed.person_id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 ");
        sb.append(" join ").append(TableName.AGENCY_INFO).append(" aio ")
                .append(" on ").append(" aio.id = pet.agency_id and aio.IS_DELETED = 0 ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" avy.agency_id = aio.id and avy.is_deleted = 0 ")
                .append(" and avy.is_bank_data_created = 0 and avy.is_month_end = 0 ")
                .append(" left join ").append(TableName.DEPARTMENT_INFO).append(" as dept ")
                .append(" on pet.dept_id=dept.id ")
                .append(" where ").append(SysUtils.inSql(" pet.agency_id ",agencyIds))
                .append(" order by aio.agency_code,s1 desc ");

//                .append(" where ").append(" pio.is_deleted = 0 ");//上报表中可能存在月初数据，当月消减人员不做删除判断
        return super.getjdbcTemplate().queryForList(sb.toString());
    }

    /**
     * 异常卡号明细查询
     * @param agencyId
     * @param columnList
     * @param personIds
     * @return
     */
    @Override
    public List<Map<String, Object>> selectBankPayBackMXOffer(String agencyId, List<String> columnList, List<String> personIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.sala_card_bank salaCardBank, ")
                .append(" avy.agency_code agencyCode, ")
                .append(" avy.agency_name agencyName, ")
                .append(" if(pet.state = '3', '在职', '离退休') state, ")
                .append(" if(pet.merit_pay_code is null or pet.merit_pay_code = '',0,pet.merit_pay_code) meritPayCode, ")
                .append(" if(pet.sala_gr_code is null or pet.sala_gr_code = '',0,sala_gr_code) salaGrCode, ")
                .append(" ifnull(dept.dept_code,'999') deptId,")
                .append(" ifnull(pet.show_order,'999') showOrder,")
                .append(" concat(avy.salary_month,pet.person_id) personCode, ")
                .append(" pet.sala_card_no salaCardNo, ")
                .append(" pet.person_name personName, ")
                .append(" pet.iden_no idenNo, ")
                .append(" if(smed.s1 is null,0,smed.s1) ns1 ,")
                .append(" if(smed.s2 is null,0,smed.s2) ns2 ");
        if(!CollectionUtils.isEmpty(columnList)){
            for(String column : columnList){
                if(!"0".equals(column)){
                    sb.append(" , if(smed.").append(column).append(" is null ")
                            .append(" or smed.").append(column).append(" = 0, '0', smed.").append(column)
                            .append(") ").append(column);
                }
            }
        }
        sb.append(" from ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" smed.person_id = pet.person_id ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" avy.agency_id = pet.agency_id and avy.is_deleted = 0 ")
                .append(" and avy.is_bank_data_created = 1 and avy.is_month_end = 0 ")
                .append(" left join ").append(TableName.DEPARTMENT_INFO).append(" as dept ")
                .append(" on pet.dept_id=dept.id ")
                .append(" where ").append(SysUtils.inSql("smed.person_id", personIds))//异常卡号通过身份证号过滤
                .append(" and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        params.add(agencyId);
        return super.getjdbcTemplate().queryForList(sb.toString(), params.toArray());
    }

    @Override
    public List<Offer> selectAgencyBankPersonCount(String province, String sendBankCode) {
        List<String> params=new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" pet.sala_card_bank salaCardBank,avy.agency_code agencyCode,count(*) personCount ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" on ").append("  pet.agency_id = smed.agency_id and pet.person_id=smed.person_id ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append("  pet.agency_id=avy.agency_id and smed.agency_id=avy.agency_id ")
                .append(" where ").append(" avy.mof_div_code = ? and avy.is_month_end = 0 ");
        params.add(province);
        if(!StringUtils.isEmpty(sendBankCode)){
            sb.append(" and pet.sala_card_bank=? ");
            params.add(sendBankCode);
        }
        sb.append(" group by pet.sala_card_bank,avy.agency_code ");
        return super.getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(Offer.class), params.toArray());
    }
}
